﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
using Microsoft.VisualBasic;


namespace Tab2Sql
{
    /// <summary>
    ///
    /// </summary>
    public static class Strings
	{
        /// <summary>
        ///
        /// </summary>
        /// <param name="x">
        /// A <see cref="System.Int32"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.String"/>
        /// </returns>
		public static string Chr(int x)
		{
			/*
			 * TODO : Something....
			*/
			return "foo";
		}
		/// <summary>
        ///
        /// </summary>
        /// <param name="s">
        /// A <see cref="System.String"/>
        /// </param>
        /// <param name="x">
        /// A <see cref="System.Int32"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.String"/>
        /// </returns>
		public static string Mid(string s, int x)
		{
			return s;
			
		}
        /// <summary>
        ///
        /// </summary>
        /// <param name="s">
        /// A <see cref="System.String"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.Int32"/>
        /// </returns>
		public static int Len(string s)
		{
			return 0;
		}
        /// <summary>
        ///
        /// </summary>
        /// <param name="s">
        /// A <see cref="System.String"/>
        /// </param>
        /// <param name="x">
        /// A <see cref="System.Int32"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.String"/>
        /// </returns>
		public static string Left(string s, int x)
		{
			return DGen.DText.Left(s,x);
		}
        /// <summary>
        /// 
        /// </summary>
        /// <param name="s">
        /// A <see cref="System.String"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.String"/>
        /// </returns>
		public static string Trim(string s)
		{
			// TODO : Implement.
			return s;
		}
		
	}
	/// <summary>
	/// Unpleasant hack to cover for the lack of microsoft.visualbasic in mono 
	/// </summary>
	public static class Constants
	{
        /// <summary>
        ///
        /// </summary>
		public static string vbLf="\r";
        /// <summary>
        ///
        /// </summary>
		public static string vbCrLf ="\r\n";
        /// <summary>
        ///
        /// </summary>
		public static string vbCr="\n";
        /// <summary>
        ///
        /// </summary>
		public static string vbTab="\t";
	}
	
	//TODO INTEGRATE WITH OTHER SQL TRANSLATE STUFF
    /// <summary>
    /// Note : I did not write the below code. Can't recall where I got it from, but some newsgroup post or similar. 
    /// - DL
    /// </summary>
    /// 
    
    public static class MySql2SqlServer
    {
        /// <summary>
        ///
        /// </summary>
        /// <param name="sMySQL">
        /// A <see cref="System.String"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.String"/>
        /// </returns>
        public static string ConvertInsert(string sMySQL)
        {
            string ConvertedSQL = "";
            ConvertedSQL = sMySQL;
            ConvertedSQL = ConvertedSQL.Replace("rn", "");
            ConvertedSQL = ConvertedSQL.Replace("'", "''");
            ConvertedSQL = ConvertedSQL.Replace("\\" + Strings.Chr(34).ToString(), Strings.Chr(34).ToString());
            //ConvertedSQL = ConvertedSQL.Replace("INTO " + this.txtOldTableName.Text, "INTO " + this.txtNewTableName.Text);
            return ConvertedSQL;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="sMySQL">
        /// A <see cref="System.String"/>
        /// </param>
        /// <param name="sMSSQL">
        /// A <see cref="System.String"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.String"/>
        /// </returns>
        public static string ConvertTable(string sMySQL, ref string sMSSQL)
        {

            // Setup variables for the code
            string sLine = string.Empty;
            int iC = 0;

            string[] sColumnParts = null;
            int iColumnPartIndex = 0;
            string sVarName = string.Empty;
            string sDatatype = string.Empty;
            string sNullable = string.Empty;
            string sDefault = string.Empty;

            string sLen = string.Empty;
            int iLen = 0;

            bool autoIncrement = false;

            string sNewDataType = string.Empty;
            string sOutput = string.Empty;
            int iLen2 = 0;
            char[] myC = new char[1];
            myC = Constants.vbLf.ToCharArray();
            string[] sLines = sMySQL.Split(myC);

            // Setup variables and some User-defined variables
            string oldSchema = "dbo";
            // The name of the schema in the source database (default 'dbo')
            string newSchema = "dbo";
          
            string tableName = string.Empty;
            bool scriptDropTable = true;
            // Whether or not to script dropping the tables in the target db
            bool fullTextEnabled = true;
            // Whether or not to generate full-text index code
            string fullTextCatalog = "FullTextCatalog";
            // The name of the full-text catalog in sql server
            string primaryKeyName = string.Empty;

            string sConstraints = string.Empty;

            // Loop through all of the lines in the mysql script code
            for (iC = 0; iC <= sLines.GetUpperBound(0); iC++)
            {
                sLine = sLines[iC];
                sLine = sLine.Trim();
                sLine = sLine.Replace(Constants.vbCr, "");
                sLine = sLine.Replace(Constants.vbLf, "");
                
                

                // Skip the blank lines
                if (string.IsNullOrEmpty(sLine))
                {
                }
                // do nothing
                else if (sLine.StartsWith("CREATE TABLE") == true)
                {

                    // Parse the table name out of the first line of the CREATE TABLE statement

                    tableName = Strings.Mid(sLine, sLine.IndexOf(".") + 3);
                    tableName = Strings.Trim(Strings.Left(tableName, tableName.LastIndexOf("`")));
                    tableName = tableName.Replace("REATE TABLE `b", "");

                    // Drop table if it exists for convenient re-run and replacement
                    if (scriptDropTable)
                    {
                        sMSSQL = sMSSQL + "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + newSchema + "].[" + tableName + "]') AND type in (N'U')) " + Constants.vbCrLf + "DROP TABLE [" + newSchema + "].[" + tableName + "] " + Constants.vbCrLf + "GO " + Constants.vbCrLf;
                    }

                    // Convert CREATE TABLE initial statement from MySql to Sql Server

                    sMSSQL = sMSSQL + Regex.Replace(sLine, "`([^`]*)`", "[$1]").Replace(oldSchema, newSchema) + Constants.vbCrLf;
                }
                else if (sLine.StartsWith("PRIMARY KEY"))
                {
                    // Convert primary key designation from MySql to Sql Server

                    // Parse the primary key field name from the mysql line of code
                    string primaryKeyField = string.Empty;
                    primaryKeyField = Strings.Mid(sLine, sLine.IndexOf("(") + 3);
                    primaryKeyField = Strings.Trim(Strings.Left(primaryKeyField, primaryKeyField.IndexOf(")") - 1));

                    // Generate a sql server alter table statement to add the primary key
                    sConstraints = sConstraints + "ALTER TABLE [" + newSchema + "].[" + tableName + "] ADD CONSTRAINT " + Constants.vbCrLf + " [PK_" + tableName.Replace(" ", "_") + "] PRIMARY KEY CLUSTERED " + Constants.vbCrLf + " ( " + Constants.vbCrLf + " [" + primaryKeyField + "] " + Constants.vbCrLf + " ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Primary]" + Constants.vbCrLf;

                    primaryKeyName = "PK_" + tableName.Replace(" ", "_");
                }
                else if (sLine.StartsWith("FULLTEXT KEY"))
                {
                    // fulltext key can only be created in Sql Server if there is a valid
                    // primary key to base it on — apparently this is not necessary in MySql
                    if (fullTextEnabled & !string.IsNullOrEmpty(primaryKeyName))
                    {

                        string fullTextName = string.Empty;
                        string fullTextField = string.Empty;

                        // Parse the full text catalog name from the code
                        fullTextName = Strings.Mid(sLine, sLine.IndexOf("`") + 2);
                        fullTextName = Strings.Left(fullTextName, fullTextName.IndexOf("`"));

                        // Parse the full text field name from the code
                        fullTextField = Strings.Mid(sLine, sLine.IndexOf("(") + 3);
                        fullTextField = Strings.Trim(Strings.Left(fullTextField, fullTextField.IndexOf(")") - 1));

                        // Generate sql server fulltext index code
                        sConstraints = sConstraints + "CREATE FULLTEXT INDEX ON [" + newSchema + "].[" + tableName + "]" + Constants.vbCrLf + "( " + Constants.vbCrLf + " [" + fullTextField + "] LANGUAGE 0 " + Constants.vbCrLf + " ) " + Constants.vbCrLf + "KEY INDEX [" + primaryKeyName + "] " + Constants.vbCrLf + "ON [" + fullTextCatalog + "] " + Constants.vbCrLf + " WITH CHANGE_TRACKING AUTO " + Constants.vbCrLf + "GO " + Constants.vbCrLf + "ALTER FULLTEXT INDEX ON [" + newSchema + "].[" + tableName + "]" + Constants.vbCrLf + "ENABLE " + Constants.vbCrLf + "GO " + Constants.vbCrLf;

                    }
                }
                else if (sLine.StartsWith(")") == true)
                {
                    // Code to finish the alter table statement when we have reached the
                    // last line with the closing parenthesis
                    sMSSQL = Strings.Left(sMSSQL, Strings.Len(sMSSQL) - 3);
                    sMSSQL = sMSSQL + Constants.vbCrLf + ")" + Constants.vbCrLf + Constants.vbCrLf;
                    sMSSQL = sMSSQL + sConstraints + Constants.vbCrLf + Constants.vbCrLf;
                    sConstraints = string.Empty;
                    primaryKeyName = string.Empty;
                    tableName = string.Empty;
                }
                else
                {
                    //Exit For

                    // Split the parts of the columnName definition for parsing
                    sColumnParts = sLine.Replace(",", "").Split(' ');
                    sVarName = sColumnParts[0];
                    sVarName = sVarName.Replace("`", "");
                    sDatatype = sColumnParts[1];
                    iColumnPartIndex = 2;

                    // Parse the data type length
                    if (sDatatype.IndexOf("(") > 0)
                    {
                        sLen = Strings.Mid(sDatatype, sDatatype.IndexOf("("));
                        sLen = sLen.Replace("(", "").Replace(")", "");
                        if (sLen.IndexOf(",") > 0)
                        {
							iLen=0;
							iLen2=0;
								
                            //iLen = (int)Conversion.Val(sLen.Split(',')[0]);
                            //iLen2 = (int)Conversion.Val(sLen.Split(',')[1]);
                        }
                        else
                        {
							iLen=0;
                            //iLen = (int)Conversion.Val(sLen);
                        }
                        sDatatype = Strings.Left(sDatatype, sDatatype.IndexOf("(") - 1);
                    }

                    autoIncrement = false;
                    sNullable = " NULL ";
                    sDefault = string.Empty;

                    // process the attributes of the columnName
                    while (iColumnPartIndex <= sColumnParts[0].Length)
                    {

                        // Parse the nullability of the field
                        if (sColumnParts[iColumnPartIndex] == "NOT")
                        {
                            sNullable = "NOT NULL";
                            iColumnPartIndex = iColumnPartIndex + 2;
                        }
                        else if (sColumnParts[iColumnPartIndex] == "NULL")
                        {
                            sNullable = "NULL";
                            iColumnPartIndex = iColumnPartIndex + 1;
                        }
                        else if (sColumnParts[iColumnPartIndex] == "auto_increment")
                        {
                            // the mysql field was auto_increment and should be identity in sql server
                            autoIncrement = true;
                            iColumnPartIndex = iColumnPartIndex + 1;
                        }
                        else if (sColumnParts[iColumnPartIndex] == "default")
                        {
                            // parse the default value designation of the columnName
                            iColumnPartIndex = iColumnPartIndex + 1;
                            while (iColumnPartIndex <= sColumnParts[0].Length)
                            {
                                sDefault = sDefault + sColumnParts[iColumnPartIndex] + " ";
                                iColumnPartIndex = iColumnPartIndex + 1;
                            }
                        }
                        else
                        {
                            iColumnPartIndex = iColumnPartIndex + 1;

                        }
                    }

                    // Add default constraint if the field has a default value
                    if (!string.IsNullOrEmpty(sDefault))
                    {
                        sConstraints = sConstraints + "ALTER TABLE [" + newSchema + "].[" + tableName + "] ADD CONSTRAINT " + Constants.vbCrLf + " DF_" + tableName.Replace( " ", "_") + "_" + sVarName.Replace(" ", "_") + " DEFAULT " + sDefault + " FOR [" + sVarName + "] " + Constants.vbCrLf + "GO " + Constants.vbCrLf;
                    }

                    if (sDatatype.IndexOf(",") > 0)
                    {
                        sDatatype = sDatatype.Split(',')[0];
                    }

                    // Convert the DataType from MySql to Sql Server
                    switch (sDatatype.ToLower())
                    {
                        case "bigint":
                        case "datetime":
                        case "timestamp":
                        case "real":
                            // types that are the same
                            sNewDataType = sDatatype;

                            break;
                        case "tinyint":
                            // integer types
                            sNewDataType = "tinyint";
                            break;
                        //sNewDataType = "smallint" signed tinyint's should go to smallint instead
                        case "smallint":
                            sNewDataType = "smallint";
                            break;
                        case "int":
                        case "mediumint":
                        case "integer":
                            sNewDataType = "int";

                            break;
                        case "tinytext":
                            //string types
                            sNewDataType = "varchar(256)";
                            break;
                        case "text":
                        case "mediumtext":
                        case "longtext":
                            sNewDataType = "text";
                            break;
                        case "varchar":
                            sNewDataType = "varchar(" + iLen + ")";
                            break;
                        case "char":
                            sNewDataType = "char(" + iLen + ")";

                            break;
                        case "tinyblob":
                            //blob types
                            sNewDataType = "binary(256)";
                            break;
                        case "blob":
                            sNewDataType = "varbinary(max)";
                            break;
                        case "mediumblob":
                        case "longblob":
                            sNewDataType = "image";

                            break;
                        case "float":
                            //decimal types
                            if (iLen <= 24)
                            {
                                sNewDataType = "float(0)";
                            }
                            else
                            {
                                sNewDataType = "float(25)";
                            }

                            break;
                        case "decimal":
                            sNewDataType = "decimal(" + iLen + "," + iLen2 + ")";
                            break;
                        case "numeric":
                            sNewDataType = "numeric(" + iLen + "," + iLen2 + ")";
                            break;
                        case "double":
                            sNewDataType = "float(25)";
                            break;
                        case "double precision":
                            sNewDataType = "float(53)";

                            break;
                        case "date":
                        case "time":
                        case "year":
                            //datetime types
                            sNewDataType = "smalldatetime";

                            break;
                        default:
                            sOutput = sOutput + "Unknown datatype = '" + sDatatype + "'";
                            break;
                    }

                    // Add the code for the columnName to the sql server statement
                    sMSSQL = sMSSQL + Constants.vbTab + "[" + sVarName + "] " + sNewDataType + " " + sNullable;
                    if (autoIncrement)
                    {
                        sMSSQL = sMSSQL + " IDENTITY (1, 1)";
                    }

                    sMSSQL = sMSSQL + "," + Constants.vbCrLf;

                }
            }

            if (string.IsNullOrEmpty(sOutput))
            {
                sOutput = "Conversion Complete";
            }

            return sOutput;
        }

    }
}
